
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 01/12/2014 11:59:32
-- Generated from EDMX file: C:\Chimera\Chimera\Common\Models\ChimeraModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [Chimera];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_NodeRole]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Roles] DROP CONSTRAINT [FK_NodeRole];
GO
IF OBJECT_ID(N'[dbo].[FK_RoleRoleType]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[RoleTypes] DROP CONSTRAINT [FK_RoleRoleType];
GO
IF OBJECT_ID(N'[dbo].[FK_EnvironmentNode]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Nodes] DROP CONSTRAINT [FK_EnvironmentNode];
GO
IF OBJECT_ID(N'[dbo].[FK_JobJobType]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[JobTypes] DROP CONSTRAINT [FK_JobJobType];
GO
IF OBJECT_ID(N'[dbo].[FK_JobJobStatus]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[JobStatuses] DROP CONSTRAINT [FK_JobJobStatus];
GO
IF OBJECT_ID(N'[dbo].[FK_JobNode]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Nodes] DROP CONSTRAINT [FK_JobNode];
GO
IF OBJECT_ID(N'[dbo].[FK_RecipeStep]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Steps] DROP CONSTRAINT [FK_RecipeStep];
GO
IF OBJECT_ID(N'[dbo].[FK_StepStep]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Steps] DROP CONSTRAINT [FK_StepStep];
GO
IF OBJECT_ID(N'[dbo].[FK_StepRole]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Roles] DROP CONSTRAINT [FK_StepRole];
GO
IF OBJECT_ID(N'[dbo].[FK_StepStep1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Steps] DROP CONSTRAINT [FK_StepStep1];
GO
IF OBJECT_ID(N'[dbo].[FK_StepJobType]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[JobTypes] DROP CONSTRAINT [FK_StepJobType];
GO
IF OBJECT_ID(N'[dbo].[FK_StepCommand]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Commands] DROP CONSTRAINT [FK_StepCommand];
GO
IF OBJECT_ID(N'[dbo].[FK_StepStep2]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Steps] DROP CONSTRAINT [FK_StepStep2];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[RoleTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[RoleTypes];
GO
IF OBJECT_ID(N'[dbo].[Roles]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Roles];
GO
IF OBJECT_ID(N'[dbo].[Nodes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Nodes];
GO
IF OBJECT_ID(N'[dbo].[Environments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Environments];
GO
IF OBJECT_ID(N'[dbo].[Jobs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Jobs];
GO
IF OBJECT_ID(N'[dbo].[JobTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[JobTypes];
GO
IF OBJECT_ID(N'[dbo].[JobStatuses]', 'U') IS NOT NULL
    DROP TABLE [dbo].[JobStatuses];
GO
IF OBJECT_ID(N'[dbo].[Recipes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Recipes];
GO
IF OBJECT_ID(N'[dbo].[Steps]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Steps];
GO
IF OBJECT_ID(N'[dbo].[Commands]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Commands];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'RoleTypes'
CREATE TABLE [dbo].[RoleTypes] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Roles'
CREATE TABLE [dbo].[Roles] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [StepId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Nodes'
CREATE TABLE [dbo].[Nodes] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Desc] nvarchar(max)  NOT NULL,
    [NetworkName] nvarchar(max)  NOT NULL,
    [Platform] nvarchar(max)  NOT NULL,
    [EnvironmentId] uniqueidentifier  NOT NULL,
    [StepId] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Environments'
CREATE TABLE [dbo].[Environments] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Jobs'
CREATE TABLE [dbo].[Jobs] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Desc] nvarchar(max)  NOT NULL,
    [CreatedOn] datetime  NOT NULL,
    [StepId] uniqueidentifier  NULL
);
GO

-- Creating table 'JobTypes'
CREATE TABLE [dbo].[JobTypes] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'JobStatuses'
CREATE TABLE [dbo].[JobStatuses] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Recipes'
CREATE TABLE [dbo].[Recipes] (
    [Id] uniqueidentifier  NOT NULL,
    [CreatedOn] datetime  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Desc] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Steps'
CREATE TABLE [dbo].[Steps] (
    [Id] uniqueidentifier  NOT NULL,
    [RecipeId] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Desc] nvarchar(max)  NOT NULL,
    [OrderId] smallint  NOT NULL,
    [StepId] uniqueidentifier  NOT NULL,
    [StepStep_Step1_Id] uniqueidentifier  NULL,
    [StepStep1_Step1_Id] uniqueidentifier  NULL
);
GO

-- Creating table 'Commands'
CREATE TABLE [dbo].[Commands] (
    [Id] uniqueidentifier  NOT NULL,
    [Content] nvarchar(max)  NOT NULL,
    [StepCommand_Command_Id] uniqueidentifier  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'RoleTypes'
ALTER TABLE [dbo].[RoleTypes]
ADD CONSTRAINT [PK_RoleTypes]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Roles'
ALTER TABLE [dbo].[Roles]
ADD CONSTRAINT [PK_Roles]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Nodes'
ALTER TABLE [dbo].[Nodes]
ADD CONSTRAINT [PK_Nodes]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Environments'
ALTER TABLE [dbo].[Environments]
ADD CONSTRAINT [PK_Environments]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Jobs'
ALTER TABLE [dbo].[Jobs]
ADD CONSTRAINT [PK_Jobs]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'JobTypes'
ALTER TABLE [dbo].[JobTypes]
ADD CONSTRAINT [PK_JobTypes]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'JobStatuses'
ALTER TABLE [dbo].[JobStatuses]
ADD CONSTRAINT [PK_JobStatuses]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Recipes'
ALTER TABLE [dbo].[Recipes]
ADD CONSTRAINT [PK_Recipes]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Steps'
ALTER TABLE [dbo].[Steps]
ADD CONSTRAINT [PK_Steps]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Commands'
ALTER TABLE [dbo].[Commands]
ADD CONSTRAINT [PK_Commands]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Id] in table 'Roles'
ALTER TABLE [dbo].[Roles]
ADD CONSTRAINT [FK_NodeRole]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[Nodes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id] in table 'RoleTypes'
ALTER TABLE [dbo].[RoleTypes]
ADD CONSTRAINT [FK_RoleRoleType]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[Roles]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [EnvironmentId] in table 'Nodes'
ALTER TABLE [dbo].[Nodes]
ADD CONSTRAINT [FK_EnvironmentNode]
    FOREIGN KEY ([EnvironmentId])
    REFERENCES [dbo].[Environments]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EnvironmentNode'
CREATE INDEX [IX_FK_EnvironmentNode]
ON [dbo].[Nodes]
    ([EnvironmentId]);
GO

-- Creating foreign key on [Id] in table 'JobTypes'
ALTER TABLE [dbo].[JobTypes]
ADD CONSTRAINT [FK_JobJobType]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[Jobs]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id] in table 'JobStatuses'
ALTER TABLE [dbo].[JobStatuses]
ADD CONSTRAINT [FK_JobJobStatus]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[Jobs]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id] in table 'Nodes'
ALTER TABLE [dbo].[Nodes]
ADD CONSTRAINT [FK_JobNode]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[Jobs]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [RecipeId] in table 'Steps'
ALTER TABLE [dbo].[Steps]
ADD CONSTRAINT [FK_RecipeStep]
    FOREIGN KEY ([RecipeId])
    REFERENCES [dbo].[Recipes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RecipeStep'
CREATE INDEX [IX_FK_RecipeStep]
ON [dbo].[Steps]
    ([RecipeId]);
GO

-- Creating foreign key on [StepStep_Step1_Id] in table 'Steps'
ALTER TABLE [dbo].[Steps]
ADD CONSTRAINT [FK_StepStep]
    FOREIGN KEY ([StepStep_Step1_Id])
    REFERENCES [dbo].[Steps]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StepStep'
CREATE INDEX [IX_FK_StepStep]
ON [dbo].[Steps]
    ([StepStep_Step1_Id]);
GO

-- Creating foreign key on [Id] in table 'Roles'
ALTER TABLE [dbo].[Roles]
ADD CONSTRAINT [FK_StepRole]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[Steps]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [StepStep1_Step1_Id] in table 'Steps'
ALTER TABLE [dbo].[Steps]
ADD CONSTRAINT [FK_StepStep1]
    FOREIGN KEY ([StepStep1_Step1_Id])
    REFERENCES [dbo].[Steps]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StepStep1'
CREATE INDEX [IX_FK_StepStep1]
ON [dbo].[Steps]
    ([StepStep1_Step1_Id]);
GO

-- Creating foreign key on [Id] in table 'JobTypes'
ALTER TABLE [dbo].[JobTypes]
ADD CONSTRAINT [FK_StepJobType]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[Steps]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [StepCommand_Command_Id] in table 'Commands'
ALTER TABLE [dbo].[Commands]
ADD CONSTRAINT [FK_StepCommand]
    FOREIGN KEY ([StepCommand_Command_Id])
    REFERENCES [dbo].[Steps]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StepCommand'
CREATE INDEX [IX_FK_StepCommand]
ON [dbo].[Commands]
    ([StepCommand_Command_Id]);
GO

-- Creating foreign key on [StepId] in table 'Steps'
ALTER TABLE [dbo].[Steps]
ADD CONSTRAINT [FK_StepStep2]
    FOREIGN KEY ([StepId])
    REFERENCES [dbo].[Steps]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StepStep2'
CREATE INDEX [IX_FK_StepStep2]
ON [dbo].[Steps]
    ([StepId]);
GO

-- Creating foreign key on [StepId] in table 'Nodes'
ALTER TABLE [dbo].[Nodes]
ADD CONSTRAINT [FK_StepNode]
    FOREIGN KEY ([StepId])
    REFERENCES [dbo].[Steps]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StepNode'
CREATE INDEX [IX_FK_StepNode]
ON [dbo].[Nodes]
    ([StepId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------